erDiagram
GAME_DEVELOPER {
string Username
int GAME_DEV_id PK
}
SPRITE_ARTIST {
string Username
int SPRITE_ARTIST_id PK
}
SOUND_DESIGNER {
string Username
int SOUND_DESIGNER_id PK
}
VIDEO_GAME {
string title
int game_id PK
string genre
int suggested_age_for_players
}
NPC {
string NPC_Name
int NPC_id PK
int SPRITE_ID
int Projectile_use_ID
int starting_health
int contact_damage
int defense
int armor
int speed
int aggro_range
string AI_type
string biome
bool spawns_during_day
bool spawns_at_night
bool is_boss
bool is_miniboss
string team
int spawn_chance
int spawn_cap_contribution
bool ignores_spawn_cap
int max_number_allowed
int number_defeated_by_player
int player_deaths
bool spawns_on_easy_diff
bool spawns_on_mediumcore_diff
bool spawns_on_hardcore_diff
int SOUND_ID
int ITEM_DROPS_ID
}
SPRITE{
string Sprite_Name
int SPRITE_ID PK
}
SOUND{
string Sound_Name
int SOUND_ID PK
int Length
}
PROJECTILE {
string Projectile_Name
int PROJECTILE_id PK
int velocity
string AI_type
int lifespan
int starting_health
int contact_damage
int secondary_proj_id
}
ITEM_DROPS {
int ITEM_id PK
int drop_chance
}
GAME_DEVELOPER ||--o{ VIDEO_GAME : "contributes to"
SPRITE_ARTIST ||--o{ VIDEO_GAME : "contributes to"
SOUND_DESIGNER ||--o{ VIDEO_GAME : "contribues to"
VIDEO_GAME ||--o{ NPC : "contains"
NPC }o--o{ PROJECTILE : "Summons or attacks with"
NPC ||--|| SPRITE : "uses"
PROJECTILE }o--o{ PROJECTILE : "Summons or attacks with"
NPC ||--|| VIDEO_GAME : "belongs to"
NPC ||--o{ ITEM_DROPS : "Drops when defeated"
NPC }o--o{ SOUND : "Plays"
Deliverable 12 - Final Project Deliverable
Links
The database implementation will actualize the requirements described in the design document and result in a working database hosted in the cloud and accessible through a web interface. The web interface will implement examples of CRUD operations (e.g., Create, Retrieve, Update, and Delete).
Problem Description
Problem Domain
: A video game may have hundreds of possible enemies. It wouldn’t make sense for the information about these enemies to be scattered around randomly in the game code. There needs to be some database to manage all of the enemy types and all of their attributes in order to efficiently manage game assets. This is the context in which our database will exist: a game development environment that prioritizes efficient and reasonable management and retrieval of game assets.
Need
: A modern video game may contain hundreds of unique enemy types, with each possessing dozens of attributes. Furthermore, each can interact with the game world in a number of ways in order to create a complex, sophisticated, and fun gaming experience. Having a single central database containing all enemy data can streamline development and enable better management of game assets. Further, it can allow developers to have a single convenient location where they always know certain parts of game code are going to be. They have to be somewhere, so it makes sense for developers to have a searchable database with all of the enemies and their attributes in a single location.
Further, a central enemy database can enable better player progression and progression of game state. Terraria, the game that inspired this project idea, has tons of derivative information about enemies (how many of each of them the player has killed, how many times each of them has killed the player, how much money they’ve picked up, and from who.) All of this and more can be saved as a part of the enemy database, allowing for more fun, interesting, and enjoyable game play by keeping track of gameplay statistics.
Context, Scope, and Perspective
: This database is for developers of the hypothetical game. It is designed to streamline the development experience, manage game assets, and enable new forms of player progression and facilitate progession of game state.
User Roles and Use Cases
: There are three general roles in this database. The first is the game developer role, which manages and contributes to the game data regarding various enemies. The second is the sprite artist who designs the in game appearence of enemies, but does not contribute greatly to the game code. The third are the sound designers, who will have access to the sound table only. The first will have access to the entire database, but the sprite artist role will only have access to the sprite table, and sound designers will only have access to the sound table.
Security and Privacy
: There database will only allow verified users (actual contributors to the game) may interact with it. In order to acheive this goal, the database will need user authentication to verify the identity of people attempting to access it. It also employs access control, such that game devs cannot mess with the sprite table, and artists cannot mess with the game code.
Web interface design:
This project is hosted using docker and nginx, SQL for the database, and PHP/HTML for the website.
Screenshots:
This is the login screen
This screen show that tables in the database
This screen shows the contents of the table ITEM_DROPS
This screen shows the contents of the table NPC
This screen shows the contents of the table PROJECTILE
This screen shows the contents of the table SOUND
This screen shows the contents of the table SOUND_DESIGNER
This screen shows the contents of the table SPRITE
This screen shows the contents of the table SPRITE_ARTIST
This screen shows the contents of the table VIDEO_GAME
This screen shows the contents of the table GAME_DEVELOPER
This is the screen to create a new record in GAME_DEVELOPER
This is the screen to update a record in GAME_DEVELOPER
This is the screen that shows all the reports
This screen shows an example of a report
Future considerations:
What needs to be done next to take your project to the next level? If you had more time, what would you do next? To take this project to the next level, we would enhance the user interface and conduct more tests. If we had more time, the priority would be an awesome user interface.
Reflections on the overall project:
Achievements:
-Successfully implemented a working database that satisfies the requirements of the design document. -Integrated the database with a web interface to showcase CRUD operations effectively. Created an ER diagram that outlines the database structure and relationships. -Ensured data integrity through the use of primary keys, foreign keys, and constraints.
Challenges:
-Time constraints limited the depth of certain features, such as advanced user authentication and real-time data integration with a game engine. -Developing the web interface to be fully interactive and polished proved to be more complex than anticipated, leaving room for improvement.
ER Diagram
The report includes a crows-foot ER diagram that visually describes the database solution and architecture.
DB integrity
The tables include foreign keys and other integrity constraints as relevant to the problem and solution.
The foreign keys ensure that the relationships between tables are maintained, and referential integrity is enforced. For instance, the NPC table has foreign keys referencing the SPRITE, PROJECTILE, SOUND, and ITEM_DROPS tables, ensuring that an NPC’s attributes are linked to valid entities in these tables. Additionally, primary keys (such as GAME_DEV_id in GAME_DEVELOPER) uniquely identify records, while foreign keys (like GAME_DEV_id in VIDEO_GAME) maintain referential integrity between related tables. These constraints prevent orphaned records and ensure consistency across the database.